package zozopo.background.operator.operations;

import java.io.IOException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.json.JSONException;
import org.json.JSONObject;

import zozopo.comm.Encrypt;
import zozopo.database.BusiDatabaseManager;
import zozopo.database.SqlMachine;

import zozopo.operations.comm.OperatingService;

public class OperatorOS extends OperatingService{
	
	//private static final Log logger = LogFactory.getLog(OperatorOS.class);
	
	public OperatorOS(Connection conn){
		super(conn);
		
	}
	
	public OperatorOS(){
		this.databaseConnection = new BusiDatabaseManager();
		this.conn = databaseConnection.getConnection();
	}
	
	/**
	 * @version 1
	 * @param String operator,---> 操作员名称
	 * @author GEE
	 * @return boolean  
	 * @throws IOException 
	 * @see isExist(String operator,String password)--->判断操作员是否存在
	 */
	
	public boolean isExistEmail(String email) throws IOException{
		boolean eResult = false;
		
		String sql = "select * from "+OperatorTB.TABLE_NAME+" where "+OperatorTB.EMAIL_OPERATOR+"='"+email+"'";
		try {
			ResultSet result = databaseConnection.executeQuery(conn,sql);
		
			if(result.next()){
				eResult = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return eResult;
	}
	
	/**
	 * @version 1.0
	 * @param String username,---> 用户名
	 * @author gee
	 * @return boolean  
	 * @throws IOException 
	 * @see isExist(String operator,String password)--->判断用户是否存在
	 */
	
	public boolean isExist(String email,String qq) throws IOException{
		boolean eResult = false;
		
		String sql = "select * from "+OperatorTB.TABLE_NAME+" where "+OperatorTB.EMAIL_OPERATOR+"='"+email+"'" + " and " + OperatorTB.QQ_OPERATOR  +"='" + qq + "'";
		
		//System.out.println(sql);
		if(logger.isDebugEnabled()){
			logger.debug("isExist() sql:"+sql.toString());
		}
		try {
			ResultSet result = databaseConnection.executeQuery(conn,sql);
		
			if(result.next()){
				eResult = true;
			}
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return eResult;
	}
	
	

	/**
	 * @version 1.0
	 * @param String operator ,String password---> 操作员名称,操作员密码
	 * @author GEE
	 * @return boolean --->输入操作员是否正确
	 * @see isRealOperator(String operator,String password) ---> 输入操作员是否正确
	 */
	public boolean isRealOperator(String operatorEmail,String password) throws IOException{
		String sql = "select * from "+OperatorTB.TABLE_NAME+" where "+OperatorTB.EMAIL_OPERATOR+"='"+operatorEmail+"' and "+OperatorTB.PASSWORD_OPERATOR+"='"+Encrypt.MessageDigest(password)+"'";
		
		boolean eResult = false;
		try {
			ResultSet result = databaseConnection.executeQuery(conn,sql);
			if(result.next()){
				eResult = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return eResult;
	}

	/**
	 * @version 0.2
	 * @param String operator---> 密码
	 * @author GEE
	 * @return OperatorInfo
	 */
	public OperatorTB getInfoByEmail(String email){
		OperatorTB operatorInfo = null;
		
		String sql = "select * from " + OperatorTB.TABLE_NAME + " where " +OperatorTB.EMAIL_OPERATOR+"='"+email+"'";
		
		List<OperatorTB> list = selectListBySql(sql);
		if(list.size() >0){
			operatorInfo = list.get(0);
		}
		
		return operatorInfo;
	}
	
	/**
	 * @version 0.2
	 * @param String operator---> 密码
	 * @author GEE
	 * @return OperatorInfo
	 */
	public OperatorTB getInfo(int id){
		OperatorTB operatorInfo = null;
		
		String sql = "select * from " + OperatorTB.TABLE_NAME + " where " +OperatorTB.ID_OPERATOR+"="+id;
		
		List<OperatorTB> list = selectListBySql(sql);
		if(list.size() >0){
			operatorInfo = list.get(0);
		}
		
		return operatorInfo;
	}
	
	/**
	 * @author GEE
	 */
	public Map<String,OperatorTB> getOperatorInfoMap(List<OperatorlimitedTB> operatorlimitedList){
		
		
		String inStr = "(";
		boolean isFirst = true;
		
		if(operatorlimitedList == null){
			return null;
		}
		
		for(OperatorlimitedTB operatorlimited:operatorlimitedList){
			if(isFirst){
				inStr += "'"+operatorlimited.getOperatoremail_operatorlimited()+"'";
				isFirst = false;
			}else{
				inStr += ","+"'"+operatorlimited.getOperatoremail_operatorlimited()+"'";
			}
		}
		inStr +=")";
		
		Map<String,OperatorTB> operatorInfoMap = new HashMap<String,OperatorTB>();
		
	
		
		String sql = "select * from " + OperatorTB.TABLE_NAME + " where " +OperatorTB.EMAIL_OPERATOR+" IN " + inStr + "";
		
		List<OperatorTB> list = selectListBySql(sql);
		if(list.size() >0){
			for(OperatorTB Operator:list){
				operatorInfoMap.put(Operator.getEmail_operator(), Operator);
			}
		}
		
		return operatorInfoMap;
	}
	
	/**
	 * @version 1.0
	 * @author GEE
	 */
	public List<OperatorTB> selectListBySql(String sql){
		//String sql = "select * from "+UserTB.TABLE_NAME+" order by  "+UserTB.USER_PRESTIGE+" desc limit "+beginPageNum+","+pageSize; 
		
		List<OperatorTB> list = null;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		
		if(resultSet != null){
			try {
				list = new ArrayList<OperatorTB>();
				while(resultSet.next()){
					OperatorTB operator = new OperatorTB();
					
					operator.setAddress_operator(resultSet.getString(OperatorTB.ADDRESS_OPERATOR));
					operator.setCreateddate_operator(resultSet.getString(OperatorTB.CREATEDDATE_OPERATOR));
					
					operator.setDepartmentid_operator(resultSet.getInt(OperatorTB.DEPARTMENTID_OPERATOR));
					operator.setDepartmentlevel_operator(resultSet.getInt(OperatorTB.DEPARTMENTLEVEL_OPERATOR));
					operator.setDepartmentname_operator(resultSet.getString(OperatorTB.DEPARTMENTNAME_OPERATOR));
					
					operator.setDes_operator(resultSet.getString(OperatorTB.DES_OPERATOR));
					operator.setEmail_operator(resultSet.getString(OperatorTB.EMAIL_OPERATOR));
					operator.setFirstname_operator(resultSet.getString(OperatorTB.FIRSTNAME_OPERATOR));
					operator.setId_operator(resultSet.getInt(OperatorTB.ID_OPERATOR));
					operator.setLastname_operator(resultSet.getString(OperatorTB.LASTNAME_OPERATOR));
					operator.setLimitedflag_operator(resultSet.getString(OperatorTB.LIMITEDFLAG_OPERATOR));
					operator.setMobile_operator(resultSet.getString(OperatorTB.MOBILE_OPERATOR));
					
					operator.setName_operator(resultSet.getString(OperatorTB.NAME_OPERATOR));
					operator.setOffice_operator(resultSet.getString(OperatorTB.OFFICE_OPERATOR));
					operator.setPhone_operator(resultSet.getString(OperatorTB.PHONE_OPERATOR));
					operator.setQq_operator(resultSet.getString(OperatorTB.QQ_OPERATOR));
					operator.setState_operator(resultSet.getString(OperatorTB.STATE_OPERATOR));
					
					list.add(operator);
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return list;
	}
	
	/**
	 * @version 1.0
	 * @author GEE
	 */
	public List<OperatorTB> select(){
		String sql = "select * from "+OperatorTB.TABLE_NAME; 
		return selectListBySql(sql);
	}
	
	/**
	 * @version 1.0
	 * @author gee
	 */
	
	public boolean isRealOperatorByEmail(String email,String passowrd) throws IOException, NoSuchAlgorithmException, SQLException{
		String sql = "select * from "+OperatorTB.TABLE_NAME+" where "+OperatorTB.EMAIL_OPERATOR+"='"+email+"' and "+OperatorTB.PASSWORD_OPERATOR+"='"+Encrypt.MessageDigest(passowrd)+"'";
		System.out.println(sql.toString());
		if(logger.isDebugEnabled()){
			logger.debug("isRealUserByEmail() sql:"+sql.toString());
		}
		boolean eResult = false;
		try {
			ResultSet result = databaseConnection.executeQuery(conn, sql);
			if(result.next()){
				eResult = true;
			}
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return eResult;
	}
	
	/**
	 * @version 1.0
	 * @author Gee
	 * @see userFlagLeve(UserTB user,int flag,int leve)
	 */
	
	public static boolean operatorFlagLeve(OperatorTB operator,int flag,int leve){
		if(flag > 128){
			return false;
		}
		if(leve > 9){
			return false;
		}
		//user.getUser_limitflag();
		Character limitflag = operator.getLimitedflag_operator().charAt(flag);
		Integer intLimitflag = Integer.parseInt(limitflag.toString());
		
		if(intLimitflag >= leve){
			return true;
		}else{
			return false;
		}
		
		
	}
	
	/**
	 * @version 1.0
	 * @author Gee
	 */
	
	public boolean updatePassowrdByEmial(String email,String newpassword){
		SqlMachine sqlMachine = new SqlMachine(conn);
		return sqlMachine.updateRow(OperatorTB.TABLE_NAME, OperatorTB.EMAIL_OPERATOR, email, OperatorTB.PASSWORD_OPERATOR, Encrypt.MessageDigest(newpassword));
	}
	
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean update(OperatorTB operator){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		updateMap.put(OperatorTB.ADDRESS_OPERATOR, operator.getAddress_operator());
		updateMap.put(OperatorTB.FIRSTNAME_OPERATOR, operator.getFirstname_operator());
		
		updateMap.put(OperatorTB.LASTNAME_OPERATOR, operator.getLastname_operator());
		updateMap.put(OperatorTB.MOBILE_OPERATOR, operator.getMobile_operator());
		updateMap.put(OperatorTB.OFFICE_OPERATOR, operator.getOffice_operator());
		
		updateMap.put(OperatorTB.PHONE_OPERATOR, operator.getPhone_operator());
		
		updateMap.put(OperatorTB.QQ_OPERATOR, operator.getQq_operator());
		
		updateMap.put(OperatorTB.DEPARTMENTID_OPERATOR, operator.getDepartmentid_operator());
		updateMap.put(OperatorTB.DEPARTMENTLEVEL_OPERATOR, operator.getDepartmentlevel_operator());
		updateMap.put(OperatorTB.DEPARTMENTNAME_OPERATOR, operator.getDepartmentname_operator());
		
		return sqlMachine.updateRow(OperatorTB.TABLE_NAME, updateMap, OperatorTB.ID_OPERATOR, operator.getId_operator());
	}
	/**
	 * @version 1.0
	 * @author Gee
	 */
	public List<OperatorTB> selectBySearchStr(String searchStr,int beginPageNum,int pageSize){
		String sql = "select * from "+OperatorTB.TABLE_NAME+" WHERE  "+OperatorTB.EMAIL_OPERATOR+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorTB.NAME_OPERATOR+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorTB.FIRSTNAME_OPERATOR+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorTB.LASTNAME_OPERATOR+" LIKE '%"+searchStr+"%'"
		+" limit "+beginPageNum+","+pageSize; 
		//ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		return selectListBySql(sql);
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForSearchStr(String searchStr){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + OperatorTB.TABLE_NAME +" WHERE  "
		+OperatorTB.NAME_OPERATOR+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorTB.FIRSTNAME_OPERATOR+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorTB.LASTNAME_OPERATOR+" LIKE '%"+searchStr+"%'";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	
	/**
	 * @version 1.0
	 * @author gee
	 * @see 
	 */
	
	public static String jsonLimitFlagToUserLimitFlag(String jsonlimitflag,String operatorlimitflag){
		StringBuffer limitflag = new StringBuffer();
		
		JSONObject jo;
		try {
			jo = new JSONObject(jsonlimitflag);
			
			char[] limitChar = operatorlimitflag.toCharArray();
			
			HashMap<String,String> hm = jo.getHashMap();
			
			for(Entry<String,String> entry:hm.entrySet()){
				if(entry.getValue().toLowerCase().equals("false")){
					//entry.setValue(0);
					entry.setValue("0");
				}else if(entry.getValue().toLowerCase().equals("true")){
					entry.setValue("1");
				}
				limitChar[Integer.parseInt(entry.getKey())] = entry.getValue().charAt(0);
			}
			
			for(char c:limitChar){
				limitflag.append(c);
			}
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		
		return limitflag.toString();
	}
	
	/**
	 * @version 1.0
	 * @author Gee
	 */
	
	public boolean updateLimitByEmail(String email,String limitflag) {
		SqlMachine sqlMachine = new SqlMachine(conn);
		
		return sqlMachine.updateRow(OperatorTB.TABLE_NAME, OperatorTB.EMAIL_OPERATOR, email, OperatorTB.LIMITEDFLAG_OPERATOR, limitflag);
		
	}
}